package opd.db.services.impl;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import opd.db.helpers.Query;
import opd.db.services.AbstractJDBCTemplateDao;
import opd.db.services.IReport3Dao;
import opd.domain.Report3;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.transaction.annotation.Transactional;

public class Report3Dao extends AbstractJDBCTemplateDao implements IReport3Dao {

	
	// -------------------- //
	// -- SQL Statements -- // --------------------------------------------------------------------
	// -------------------- //
	
    private final String SQL_GET_DAILY_REPORT = "SELECT DR3_ID, WORKING_DATE, ALPHA_BETA_USER, ENTRIES, DIFF_OF_TARGET, TARGET_TOTALS, USR_CODE " +
            " FROM DAILY_REPORT3 INNER JOIN TARGETS ON DAILY_REPORT3.TARGET_ID = TARGETS.TARGET_ID " +
            " WHERE WORKING_DATE = ?";

    private final String SQL_GET_DAILY_REPORT_BY_USER_ID = "SELECT DR3_ID, WORKING_DATE, ALPHA_BETA_USER, ENTRIES, DIFF_OF_TARGET, TARGET_TOTALS, USR_CODE " +
            " FROM DAILY_REPORT3 INNER JOIN TARGETS ON DAILY_REPORT3.TARGET_ID = TARGETS.TARGET_ID " +
            " WHERE USR_CODE = ? ";
    
	// ---------------- //
	// -- Row Mapper -- // ------------------------------------------------------------------------
	// ---------------- //
    
    class Report3RowMapper implements RowMapper<Report3>{
    	private List<Date> datesBack = null;

    	// ----------------------------------------------------------------------------------------------------
		
    	public Report3RowMapper() {
			
		}
    	
    	// ----------------------------------------------------------------------------------------------------
		
		public Report3RowMapper(List<Date> datesBack) {
			this.datesBack = datesBack;
		}

		// ----------------------------------------------------------------------------------------------------

		@Override
		public Report3 mapRow(ResultSet rs, int row) throws SQLException {
			Report3 report3 = new Report3();
			
			report3.setId(rs.getInt("DR3_ID"));
			report3.setWorkingDate(rs.getDate("WORKING_DATE"));
			
			
			//report3.setAlphaBetauser(rs.getString("ALPHA_BETA_USER"));
			
			try {
				report3.setAlphaBetauser(new String (rs.getBytes("ALPHA_BETA_USER"), "ISO-8859-1"));
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			
			report3.setEntries(rs.getInt("ENTRIES"));
			report3.setDiffOfTarget(rs.getInt("DIFF_OF_TARGET"));
			report3.setTargetTotals(rs.getInt("TARGET_TOTALS"));
			report3.setUserId(rs.getString("USR_CODE"));
			
			
			if (this.datesBack !=null){
				report3.setPreviousEntries(getEntriesDatesBack(report3.getUserId(), this.datesBack));
			}
			
			return report3;
		}
    	
    }

	// ------------------------- //
	// -- Prepared Statements -- // ---------------------------------------------------------------
	// ------------------------- //

    class Report3EntriesDatesBackPreparedStatement implements PreparedStatementCreator{
    	private String sqlStatement;
    	private String userId;
    	private List<Date> datesBack;

    	// ----------------------------------------------------------------------------------------------------

		public Report3EntriesDatesBackPreparedStatement(String sqlStatement, String userId, List<Date> datesBack) {
			this.sqlStatement = sqlStatement;
			this.userId = userId;
			this.datesBack = datesBack;
		}    	

		// ----------------------------------------------------------------------------------------
		
		@Override
		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement preparedStatement = connection.prepareStatement(this.sqlStatement);
			
			int parameterIndex = 1;
			
			preparedStatement.setString(parameterIndex, this.userId);
			
			 
			for (int i=0; i<this.datesBack.size(); i++){
				parameterIndex++;
				preparedStatement.setDate(parameterIndex, new java.sql.Date(this.datesBack.get(i).getTime()));
			}
			
			return preparedStatement;
		}
    }
    
    // --------------------------------------------------------------------------------------------
    
    class Report3PreparedStatement implements PreparedStatementCreator{
    	private String sqlStatement;
    	private Date workingDate;
    	
    	// ----------------------------------------------------------------------------------------
    	
		public Report3PreparedStatement(String sqlStatement, Date workingDate) {
			this.sqlStatement = sqlStatement;
			this.workingDate = workingDate;
		}

		// ----------------------------------------------------------------------------------------

		@Override
		public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
			PreparedStatement preparedStatement = connection.prepareStatement(this.sqlStatement);
			
			preparedStatement.setDate(1, new java.sql.Date(this.workingDate.getTime()));
			
			return preparedStatement;
		}
    	
    }
	
    
	// ------------- //
	// -- Methods -- // ---------------------------------------------------------------------------
	// ------------- //
	
	@Override
	@Transactional
	public List<Report3> getDailyReport(Date workingDate, List<Date> datesBack) {
		return jdbcTemplate.query(new Report3PreparedStatement(SQL_GET_DAILY_REPORT, workingDate), new Report3RowMapper(datesBack));
	}

	// ----------------------------------------------------------------------------------------------------
	
	@Override
	@Transactional
	public List<Report3> getEntriesDatesBack(String userId, List<Date> datesBack) {
		
		Query query = new Query(Query.SELECT_QUERY);
		String[] inExpression = new String[datesBack.size()];
		for (int i=0; i<inExpression.length; i++){
			inExpression[i] = "?";
		}
		
		query.setWhere("WORKING_DATE", "IN", inExpression);
		
		String sql_get_daily_report_by_user_id_for_dates_back = String.format("%s AND %s", SQL_GET_DAILY_REPORT_BY_USER_ID, query.getWere());
		
		return jdbcTemplate.query(new Report3EntriesDatesBackPreparedStatement(sql_get_daily_report_by_user_id_for_dates_back, userId, datesBack), new Report3RowMapper());
	}

}